#!bin/bash


if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d'-1 day' +%F`
fi

ADS_REGION_ADS_REPORT_SQL="
INSERT OVERWRITE TABLE jtp_ads_warehouse.ads_region_ads_report
SELECT  * FROM jtp_ads_warehouse.ads_region_ads_report
UNION
SELECT
    '${data_date}' AS dt
     ,if(client_province = '0' , '未知',client_province) AS province
     ,if(client_city = '0' , '未知',client_city) AS city
     ,if(is_invalid_traffic, '异常','正常') AS is_invalid_traffic
     ,count(if(event_type = 'click',ads_id,NULL)) AS click_count
     ,count(ads_id) AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt = '${data_date}'
  AND event_type IN ('click','impression')
GROUP BY client_province
       ,client_city
       ,is_invalid_traffic
;

"
ADS_PLATFORM_ADS_REPORT_SQL="
INSERT OVERWRITE TABLE jtp_ads_warehouse.ads_platform_ads_report
SELECT * FROM jtp_ads_warehouse.ads_platform_ads_report
UNION
SELECT
    '${data_date}' AS dt
     ,platform_id
     ,platform_name_zh
     ,if(is_invalid_traffic, '异常','正常') AS is_invalid_traffic
     ,count(if(event_type = 'click',ads_id,NULL)) AS click_count
     ,count(ads_id) AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt = '${data_date}'
  AND event_type IN ('click','impression')
GROUP BY platform_id
       ,platform_name_zh
       ,is_invalid_traffic
;
"

ADS_OS_ADS_REPORT_SQL="
INSERT OVERWRITE TABLE jtp_ads_warehouse.ads_os_ads_report
SELECT * FROM jtp_ads_warehouse.ads_os_ads_report
UNION
SELECT
    '${data_date}' AS dt
     ,if(client_os_type = '','未知',client_os_type) AS client_os_type
     ,if(is_invalid_traffic, '异常','正常') AS is_invalid_traffic
     ,count(if(event_type = 'click',ads_id,NULL)) AS click_count
     ,count(ads_id) AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt = '${data_date}'
  AND event_type IN ('click','impression')
GROUP BY client_os_type
       ,is_invalid_traffic
;
"

ADS_HOUR_ADS_REPORT_SQL="
INSERT OVERWRITE TABLE jtp_ads_warehouse.ads_hour_ads_report
SELECT * FROM jtp_ads_warehouse.ads_hour_ads_report
UNION
SELECT
    '${data_date}' AS dt
     ,ads_id,ads_name
     ,hour(from_unixtime(event_time / 1000)) AS hour
     ,if(is_invalid_traffic, '异常','正常') AS is_invalid_traffic
     ,count(if(event_type = 'click',ads_id,NULL)) AS click_count
     ,count(ads_id) AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt = '${data_date}'
  AND event_type IN ('click','impression')
GROUP BY hour(from_unixtime(event_time / 1000))
        ,ads_id,ads_name
       ,is_invalid_traffic
;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${ADS_REGION_ADS_REPORT_SQL}${ADS_PLATFORM_ADS_REPORT_SQL}${ADS_OS_ADS_REPORT_SQL}${ADS_HOUR_ADS_REPORT_SQL}"